-- =============================================
-- This SP moves the specified Nav_Menu record to the end of a Website.
--
-- Modifications
-- 07/16/2003   E.Tatsui
-- 09/22/2005   E.Tatsui  Changed so that an item can be moved to a website w/o any nav items.
-- =============================================
CREATE     PROCEDURE amsp_CMMoveNavMenuToWebsite
  @InToMoveNavMenuID numeric,
  @InWebsiteKey varchar(50)
AS

BEGIN
  DECLARE
    @TargetNavMenuID numeric,
    @ErrorMessage varchar(255),
    @MinSort numeric(28,18),
    @MaxSort numeric(28,18)

  --Figure out what the target NavMenuID should be.
  SELECT TOP 1 @TargetNavMenuID = NavMenuID
    FROM Nav_Menu WITH (NOLOCK)
   WHERE WebsiteKey = @InWebsiteKey
     AND NavContentGroupInd = 'N'
     AND CategoryDepth = 1
   ORDER BY SortOrder DESC

  UPDATE Nav_Menu
     SET WebsiteKey = @InWebsiteKey
   WHERE NavMenuID = @InToMoveNavMenuID

  -- Build a table of item's descendants
  SELECT @MinSort = a.SortOrder,
         @MaxSort = (SELECT IsNull(Min(x.SortOrder),0)
                       FROM Nav_Menu x
                       WITH (NOLOCK)
                      WHERE x.SortOrder > a.SortOrder
                        AND x.CategoryDepth <= a.CategoryDepth)
    FROM Nav_Menu a WITH (NOLOCK)
   WHERE a.NavMenuID = @InToMoveNavMenuID
  
  SELECT a.NavMenuID, a.SortOrder
    INTO #Descendants
    FROM Nav_Menu a WITH (NOLOCK)
   WHERE a.SortOrder > @MinSort
     AND a.SortOrder < @MaxSort

  UPDATE Nav_Menu
     SET WebsiteKey = @InWebsiteKey
    FROM Nav_Menu a WITH (NOLOCK), #Descendants b
   WHERE a.NavMenuID = b.NavMenuID

  DROP TABLE #Descendants

  -- If we found out, go ahead and move to the position.
  IF @TargetNavMenuID IS NOT NULL BEGIN
    EXEC amsp_CMMoveNavMenu @InToMoveNavMenuID, @TargetNavMenuID, 'M', NULL, @ErrorMessage OUTPUT
  END
  -- Moving as the first item on the website
  ELSE BEGIN
    SELECT @TargetNavMenuID = NavMenuID
    FROM Nav_Menu WITH (NOLOCK)
   WHERE WebsiteKey = @InWebsiteKey
     AND NavContentGroupInd = 'N'
     AND CategoryDepth = 0
    EXEC amsp_CMMoveNavMenu @InToMoveNavMenuID, @TargetNavMenuID, 'M', 'Lower', @ErrorMessage OUTPUT
  END

  IF @ErrorMessage IS NOT NULL
    RAISERROR(@ErrorMessage,1,1)

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMMoveNavMenuToWebsite] TO [IMIS]
GO